﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using QLNS.Models;

namespace QLNS.aspFrm
{
    public partial class frmDanhsachCanBoPhongBanInclude : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            ITAPMNS2013Entities context = new ITAPMNS2013Entities();

            List<int> arr = PhongBanModels.GetAllIDToRootInDepartmentInclude(Convert.ToInt32(Request.QueryString["id"].ToString()));

            var dbResult = (from cb in context.DSCanBo
                            join cv in context.DSChucVu on cb.ChucVuHienTai equals cv.ID
                            join pb in context.DSPhongBan on cb.IDPhongBan equals pb.ID
                            where arr.Contains(cb.IDPhongBan)
                            //select cb).ToList();
                            select new
                            {
                                cb.ID,
                                cb.SoHieuCBCC,
                                cb.Ho,
                                cb.Ten,
                                cb.GioiTinh,
                                cb.HoKhauThuongTru,
                                cb.DiaChi,
                                TenChucVuHienTai = cv.Ten,
                                TenPhongBan = pb.TenPhongBan,
                                cb.NgaySinh,
                                cb.IDPhongBan
                            } into abc
                            orderby abc.IDPhongBan ascending, abc.SoHieuCBCC ascending
                            select abc).ToList();
            //var dbResult = db.Customer
            var customers = (from customer in dbResult
                             select new
                             {

                                 customer.ID,
                                 customer.SoHieuCBCC,
                                 customer.Ho,
                                 customer.Ten,
                                 customer.TenChucVuHienTai,
                                 customer.NgaySinh,
                                 customer.GioiTinh,
                                 customer.HoKhauThuongTru,
                                 customer.DiaChi,
                                 customer.TenPhongBan
                             });
            System.Data.DataTable dtTK = MakeTable();

            foreach (var r in customers)
            {
                System.Data.DataRow row;
                row = dtTK.NewRow();
                //row["ID"] = r.ID;
                row["sohieuCBCC"] = r.SoHieuCBCC;
                row["ho"] = r.Ho;
                row["ten"] = r.Ten;
                row["chucvu"] = r.TenChucVuHienTai;
                //row["ngaysinh"] = r.NgaySinh;

                System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
                row["ngaysinh"] = r.NgaySinh.ToString() == "" ? DateTime.Parse("01/1/1900", ci.DateTimeFormat) : r.NgaySinh;
                row["gioitinh"] = r.GioiTinh;
                row["hokhau"] = r.HoKhauThuongTru;
                row["diachi"] = r.DiaChi;
                row["phongban"] = r.TenPhongBan;

                dtTK.Rows.Add(row);
            }



            CrystalDecisions.CrystalReports.Engine.ReportDocument rd = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
            string strRptPath = Server.MapPath("~/") + "Reports//Report_DanhSachCanBoPhongBan.rpt";
            //Loading Report
            rd.Load(strRptPath);

            // Setting report data source
            if (dtTK != null && dtTK.GetType().ToString() != "System.String")
                rd.SetDataSource(dtTK);


            CrystalReportViewer1.ReportSource = rd;

        }

        private System.Data.DataTable MakeTable()
        {
            // Create a new DataTable.
            System.Data.DataTable table = new System.Data.DataTable("Table");

            // Declare variables for DataColumn and DataRow objects.
            System.Data.DataColumn column;

            // Create new DataColumn, set DataType, 

            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "sohieuCBCC";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ho";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "ten";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.DateTime");
            column.ColumnName = "ngaysinh";
            column.Caption = "ngaysinh";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "chucvu";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "phongban";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "gioitinh";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "hokhau";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            // Create second column.
            column = new System.Data.DataColumn();
            column.DataType = System.Type.GetType("System.String");
            column.ColumnName = "diachi";
            column.Caption = "shcbcc";
            // Add the column to the table.
            table.Columns.Add(column);
            return table;
        }

    }
}